package data;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import ui.MessagePopUp;

/** Representing all the db_account related
 * DAObjects to insert, update, delete
 * @author Matthias
 *
 */
public class BankDatabaseAccountSQL {

	/** Declaring the BankDatabase object **/
	private final BankDatabase bankDatabase;

	/** Constructor of this class
	 * @param bankDatabase is an BankDatabase object
	 */
	public BankDatabaseAccountSQL(final BankDatabase bankDatabase) {
		this.bankDatabase = bankDatabase;
	}

	/** Method inserts account into the table db_account
	 * @param userID is current id of user
	 * @param accNr is entered account number
	 * @param accountType is the type of the created account
	 * @param interestRate is the rate, matching the type
	 * @param totalBalance is the the total balance
	 * @throws SQLException in case a SQL error occurs
	 */
	public final void insertAccountIntoTable(final String userID, 
			final int accNr, final String accountType,
			final String interestRate, final double totalBalance) 
					throws SQLException {

		Connection conn = BankDatabase.getDBConnection();
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;

		String insertTableSQL = "INSERT INTO DB_Account("
				+ " User_ID, "
				+ " Account_Number, Account_Type, "
				+ " Interest_Rate, Total_Balance , CREATED_DATE) VALUES"
				+ " (?, ?, ?, ?, ?, ?) ";
		try {
			preparedStatement = conn.prepareStatement(insertTableSQL);
			preparedStatement.setString(1, userID);
			preparedStatement.setInt(2, accNr);
			preparedStatement.setString(3, accountType);
			preparedStatement.setString(4, interestRate);
			preparedStatement.setDouble(5, totalBalance);
			preparedStatement.setTimestamp(6, BankDatabase.getCurrentTimeStamp());
			preparedStatement.executeUpdate();

			// get User_ID, the foreign key of db_user, in order to 
			// insert the account to the matching user
			DatabaseMetaData metaData = conn.getMetaData();
			rs = metaData.getImportedKeys(null , null, "db_user");
			while (rs.next()) {
				String fkTableName = rs.getString("FKTABLE_NAME");
				String fkColumnName = rs.getString("FKCOLUMN_NAME");
				String pkTableName = rs.getString("PKTABLE_NAME");
				String pkColumnName = rs.getString("PKCOLUMN_NAME");
				System.out.println("fk table name" + fkTableName 
						+ "column name" + fkColumnName + " pk table name " 
						+ pkTableName + "column name " + pkColumnName);
			}

			int id = Integer.parseInt(userID);
			// get the current user
			User currentUser = bankDatabase.getUsers(id);
			// and adding the account to the arraylist
			currentUser.addAccount(
					new Account(accNr, accountType, interestRate, totalBalance));

			String text = "The account " + accountType
					+ " " + accNr + " is created";
			new MessagePopUp().setSuccess(text);
		}
		catch (SQLException e) {
			String text = "Problems occured, " 
					+ "inserting the account. Please try again";
			new MessagePopUp().setError(text);
		}
		finally {
			try {
				preparedStatement.close();
				conn.close();
				if (rs != null) {
					rs.close();
				}
			} catch (SQLException exc) {
				String text = "Error! Problems occured, closing the connection";
				new MessagePopUp().setError(text);
			}
		}
	}

	/** Method deletes an account from the table db_account
	 * @param accNr is the account number
	 * @throws SQLException in case a SQL error occurs
	 */	 
	public final void deleteAccountFromTable(final int accNr) throws SQLException {

		Connection conn = BankDatabase.getDBConnection();
		PreparedStatement preparedStatement = null;

		String deleteAccountSQL = "DELETE FROM DB_Account "
				+ "WHERE Account_Number=" + accNr;

		try {
			preparedStatement = conn.prepareStatement(deleteAccountSQL);
			// execute delete SQL statement
			preparedStatement.executeUpdate();
			// delete the account from arraylist;
			bankDatabase.getClassUser().removeAccount(accNr);

			String text = "Account " + accNr 
					+ " \nis deleted from " + "the database!";
			new MessagePopUp().setSuccess(text);
		}
		catch (SQLException e) {
			String text = "Error! Could not " 
					+ "delete the account. Please try again";
			new MessagePopUp().setError(text);
		}
		finally {
			preparedStatement.close();
			conn.close();
		}
	}

	/** Method updates / credits an account in the table / arraylist
	 * @param accNr is account number
	 * @param depositAmount is the entered amount in the deposit ui
	 * @throws SQLException in case a SQL exception occurs
	 */
	public final void creditAccSQL(final int accNr, 
			final double depositAmount) throws SQLException {

		Connection conn = BankDatabase.getDBConnection();
		Statement stmt = conn.createStatement();

		int userID = bankDatabase.getClassUser().getUserID();

		String addCredit = "UPDATE db_account " 
				+ "SET Total_Balance = " 
				+ (depositAmount + bankDatabase.getTotalBalance(userID))
				+ " WHERE account_number = " + accNr;
		try {
			stmt.executeUpdate(addCredit);
			// adding the amount to the account for the arraylist
			bankDatabase.credit(userID, depositAmount);

			String text = "Your envelope of " + depositAmount 
					+ " \nfor Account Number " + accNr 
					+ " \nhas been received!" + "\n\nNew balance: "
					+ bankDatabase.getTotalBalance(userID);
			new MessagePopUp().setSuccess(text);
		} 
		catch (SQLException e) {
			String text = "Error! Could not credit the account. "
					+ "Please try again";
			new MessagePopUp().setError(text);
		}
		finally {
			stmt.close();
			conn.close();
		}
	}

	/**  Method updates / debits an account in the table / arraylist
	 * @param accNr is account number
	 * @param withdrawalAmount is the entered / selected amount 
	 * in the withdrawal ui
	 * @throws SQLException in case a SQL exception occurs
	 */
	public final void debitAccSQL (final int accNr, 
			final double withdrawalAmount) throws SQLException {

		Connection conn = BankDatabase.getDBConnection();
		Statement stmt = conn.createStatement();

		int userID = bankDatabase.getClassUser().getUserID();

		String addDebit = "UPDATE db_account "
				+ "SET Total_Balance = " 
				+ (bankDatabase.getTotalBalance(userID) - withdrawalAmount)
				+ " WHERE account_number = " + accNr;
		try {
			stmt.executeUpdate(addDebit);
			// adding the update to the account for the arraylist
			bankDatabase.debit(userID, withdrawalAmount);
			String text = withdrawalAmount
					+ " has been dispensed. Please take your cash now.";
			new MessagePopUp().setSuccess(text);
		} 
		catch (SQLException e) {
			String text = 
					"Error! Could not debit the account. Please try again";
			new MessagePopUp().setError(text);
		}
		finally {
			stmt.close();
			conn.close();
		}	
	}

} // end this class